package cn.dy.workatten.dao;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import cn.dy.base.framework.db.DBConnectPool;
import cn.dy.base.framework.db.DBUtil;
import cn.dy.base.framework.db.util.DBExecuteUtil;
import cn.dy.workatten.pojo.AttendRecord;

public class AttendRecordDao {

	protected static Logger logger = LoggerFactory.getLogger(AttendRecordDao.class);

	private DataSource dataSource;
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedJdbcTemplate;

	public AttendRecordDao() {
		this.dataSource = DBConnectPool.create().getDataSource();
		if (this.jdbcTemplate == null)
			this.jdbcTemplate = new JdbcTemplate(this.dataSource);
		if (this.namedJdbcTemplate == null) {
			this.namedJdbcTemplate = new NamedParameterJdbcTemplate(this.dataSource);
		}
	}
	
	/**
	 * 获得自增系列
	 * @return
	 */
	public long getUniqueId() {
		long result = 0L;
		try {
			result = DBUtil.getSequence(new JdbcTemplate(this.dataSource), "seq_bz_attend_record");
		} catch (DataAccessException e) {
			logger.error("获得考勤记录明细自动生成的ID值失败", e);
		}
		return result;
	}

	/**
	 * 新增
	 * 
	 * @return
	 */
	public int addAttendRecord(AttendRecord attendRecord) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;

		try {
			String sql = "insert into bz_attend_record(id,dept_id,staff_id,attend_day,schedule_id,man_hour_all,man_hour_midnight,work_value,create_id,create_time) "
					+ "values(:id,:dept_id,:staff_id,:attend_day,:schedule_id,:man_hour_all,:man_hour_midnight,:work_value,:create_id,:create_time)";

			Map resultMap = new HashMap();
			resultMap.put("id", getUniqueId());
			resultMap.put("dept_id", attendRecord.getDept_id());
			resultMap.put("staff_id", attendRecord.getStaff_id());
			resultMap.put("attend_day", attendRecord.getAttend_day());
			resultMap.put("schedule_id", attendRecord.getSchedule_id());
			resultMap.put("man_hour_all", attendRecord.getMan_hour_all());
			resultMap.put("man_hour_midnight", attendRecord.getMan_hour_midnight());
			resultMap.put("work_value", attendRecord.getWork_value());
			resultMap.put("create_id", attendRecord.getCreate_id());
			resultMap.put("create_time", attendRecord.getCreate_time());
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("添加考勤记录明细失败", e);
		}
		return result;
	}

	/**
	 * 修改
	 * 
	 * @param scheduleCfg
	 * @return
	 */
	public int updateAttendRecord(AttendRecord attendRecord) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;
		try {
			String sql = "update bz_attend_record set dept_id=:dept_id,staff_id=:staff_id,attend_day=:attend_day,schedule_id=:schedule_id,man_hour_all=:man_hour_all,man_hour_midnight=:man_hour_midnight"
					+ ",work_value=:work_value,create_id=:create_id,create_time=:create_time where id=:id";

			Map resultMap = new HashMap();
			resultMap.put("id", Long.valueOf(attendRecord.getId()));
			resultMap.put("dept_id", attendRecord.getDept_id());
			resultMap.put("staff_id", attendRecord.getStaff_id());
			resultMap.put("attend_day", attendRecord.getAttend_day());
			resultMap.put("schedule_id", attendRecord.getSchedule_id());
			resultMap.put("man_hour_all", attendRecord.getMan_hour_all());
			resultMap.put("man_hour_midnight", attendRecord.getMan_hour_midnight());
			resultMap.put("work_value", attendRecord.getWork_value());
			resultMap.put("create_id", attendRecord.getCreate_id());
			resultMap.put("create_time", attendRecord.getCreate_time());
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("修改考勤记录明细失败", e);
		}
		return result;
	}

	/**
	 * 删除
	 * 
	 * @param id
	 * @return
	 */
	public int deleteAttendRecordById(long id) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;
		try {
			String sql = " delete from bz_attend_record where id=:id";
			Map resultMap = new HashMap();
			resultMap.put("id", Long.valueOf(id));
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("删除考勤记录明细失败", e);
		}
		return result;
	}
	
	
	/**
	 * 	删除某个考勤申请的所有明细
	 * 
	 * @param id
	 * @return
	 */
	public int deleteAttendRecordByDeptDate(long dept_id, Date attend_day) {
		DBExecuteUtil db = new DBExecuteUtil();
		int result = 0;
		try {
			String sql = " delete from bz_attend_record where dept_id=:dept_id";
			Map resultMap = new HashMap();
			resultMap.put("dept_id", Long.valueOf(dept_id));
			resultMap.put("attend_day", attend_day);
			
			result = db.executeSql(sql, resultMap);
		} catch (DataAccessException e) {
			logger.error("删除考勤记录明细失败", e);
		}
		return result;
	}
	

	/**
	 * 根据id查询勤记录明细信息
	 * 
	 * @param id
	 * @return
	 */
	public AttendRecord getAttendRecordById(long id) {
		DBExecuteUtil db = new DBExecuteUtil();
		AttendRecord attendRecord = new AttendRecord();
		try {
			String sql = "select * from bz_attend_record where id=:id ";
			
			Map parameters = new HashMap();
			parameters.put("id", Long.valueOf(id));
			attendRecord = (AttendRecord) db.queryObject(sql, parameters, AttendRecord.class);
		} catch (DataAccessException e) {
			logger.error("根据id获得考勤记录明细失败", e);
		}
		return attendRecord;
	}
	
	
	
	/**
	 *	 查询某个考勤申请的考勤明细列表
	 * 
	 * @param dept_id
	 * @param month
	 * @return
	 */
	public List<AttendRecord> getAttendRecordList(long dept_id, Date attend_day) {
		DBExecuteUtil db = new DBExecuteUtil();
		String sql = "select a.*,b.schedule_sub_type AS scheduleCheckType from bz_attend_record a, bz_schedule_cfg b where a.schedule_id = b.id AND a.dept_id=:dept_id and a.attend_day=:attend_day order by staff_id asc";

		Map<String, Object> params = new HashMap();
		params.put("dept_id", Long.valueOf(dept_id));
		params.put("attend_day", attend_day);

		List<AttendRecord> list = db.queryList(sql, params, AttendRecord.class);
		return list;
	}
	
	
	/**
	 *	 查询某个部门某个月的考勤明细列表（分页查询）
	 * 
	 * @param dept_id
	 * @param month
	 * @return
	 */
	public List<AttendRecord> getDeptMonthAttendRecordList(long dept_id, Date start_attend_day, Date end_attend_day) {
		DBExecuteUtil db = new DBExecuteUtil();
		String sql = "select a.*,b.schedule_sub_type AS scheduleCheckType from bz_attend_record a, bz_schedule_cfg b "
				+ "where a.schedule_id = b.id AND a.dept_id=:dept_id and a.attend_day >= :start_attend_day and a.attend_day <= :end_attend_day order by a.staff_id asc";

		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		Map<String, Object> params = new HashMap();
		params.put("dept_id", Long.valueOf(dept_id));
		params.put("start_attend_day", df.format(start_attend_day));
		params.put("end_attend_day", df.format(end_attend_day));
		
		List<AttendRecord> list = db.queryList(sql, params, AttendRecord.class);
		return list;
	}
	
	/**
	 *	 查询某个考勤申请的考勤明细列表（分页查询）
	 * 
	 * @param dept_id
	 * @param month
	 * @return
	 */
	public List<AttendRecord> getAttendRecordListByPage(long dept_id, Date attend_day, int pageNum, int pageSize, List<Long> memberIdList) {
		DBExecuteUtil db = new DBExecuteUtil();
		String sql = "select a.*,b.schedule_sub_type AS scheduleCheckType from bz_attend_record a, bz_schedule_cfg b where a.schedule_id = b.id AND a.dept_id=:dept_id and a.attend_day=:attend_day and a.staff_id in (:memberIdList) order by a.staff_id asc";

		Map<String, Object> params = new HashMap();
		params.put("dept_id", Long.valueOf(dept_id));
		params.put("attend_day", attend_day);
		params.put("memberIdList", memberIdList);
		
//		List<AttendRecord> list = this.namedJdbcTemplate.query(DBUtil.getPagingSql(sql.toString()),
//				DBUtil.parsePagingParam(params, pageNum, pageSize), new BeanPropertyRowMapper(AttendRecord.class));
		
		List<AttendRecord> list = db.queryList(sql, params, AttendRecord.class);
		return list;
	}
	
	/**
	 *	 查询某个考勤申请的考勤明细总数量
	 * 
	 * @param dept_id
	 * @param month
	 * @return
	 */
	public long getAttendRecordCount(long dept_id, Date attend_day) {
		String sql = "select a.*,b.schedule_sub_type AS scheduleCheckType from bz_attend_record a, bz_schedule_cfg b where a.schedule_id = b.id AND a.dept_id=:dept_id and a.attend_day=:attend_day order by a.staff_id asc";

		Map<String, Object> params = new HashMap();
		params.put("dept_id", Long.valueOf(dept_id));
		params.put("attend_day", attend_day);
		return this.namedJdbcTemplate.queryForLong(DBUtil.getCountSql(sql), params);
	}
	
	
	
	
	/**
	 *	 查询某个员工，某个月的考勤明细列表
	 * 
	 * @param dept_id
	 * @param month
	 * @return
	 */
	public List<AttendRecord> getAttendRecordsByStaffMon(long staff_id, Date beginTime, Date endTime) {
		DBExecuteUtil db = new DBExecuteUtil();
		String sql = "select * from bz_attend_record where staff_id=:staff_id and attend_day>=:beginTime and attend_day<=:endTime order by staff_id asc";

		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Map<String, Object> params = new HashMap();
		params.put("staff_id", Long.valueOf(staff_id));
		params.put("beginTime", df.format(beginTime));
		params.put("endTime", df.format(endTime));
		List<AttendRecord> list = db.queryList(sql, params, AttendRecord.class);
		return list;
	}
	
	
}
